﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using DevComponents.DotNetBar;
using StuMana.Services;
using System.Xml;
using MySql.Data.MySqlClient;

namespace StuMana.UI
{
    public partial class ManageAccount : Office2007Form
    {
        public ManageAccount()
        {
            InitializeComponent();
        }

        private void ManageAccount_Load(object sender, EventArgs e)
        {
            ConfigSetting();
            co = true;
            cmbExGender.Items.Add("Male");
            cmbExGender.Items.Add("Female");
            for (int i =1; i < 32; i++)
            {
                cmbExDay.Items.Add(i.ToString());
            }
            cmbExDay.SelectedIndex = 0;

            for (int j = 1; j < 13; j++)
            {
                cmbExMonth.Items.Add(j.ToString());
            }

            LoadLevelData();
            LoadAccountData();
            flag = true;
            if (dgvExDanhSach.Columns.Count > 0)
            {
                int w = dgvExDanhSach.Size.Width;
                int widthC = ((int)w / dgvExDanhSach.Columns.Count);

                for (int i = 0; i < dgvExDanhSach.Columns.Count; i++)
                {
                    dgvExDanhSach.Columns[i].Width = widthC;
                }
            }
        }
            public int level = 0 ;

         // export excel
        Services.ExcelInterface excel = null;
        
        // datagridview parameters
        List<string>[] list;
        string[] columnName;
        string query;
        string[] columnNameReal;
        int page;
        int maxpage;
        static int rowperpage;
  
        /// <summary>
        /// // serrvices
        /// </summary>
        private Services.student newstudent = new Services.student();
        private Services.SL025M rfid_reader = new Services.SL025M();
        public Services.COM connect;
        private Services.DataviewService dataview = new Services.DataviewService();

        //timer catch byteArray
        private byte[] serial_buff = new byte[40];

        private int action = 0;
     
        // infor reset
        private void FormReset()
        {
            txtXName.Text="";
            txtUserName.Text="";
            txtPassword.Text="";

            cmbExDay.SelectedIndex = 0;
            cmbExMonth.SelectedIndex = 0;
            txtXYear.Text = "";
            cmbExGender.SelectedIndex = 0;
            cmbExLevel.SelectedIndex=0;
        }

        private bool flag = false;
        private bool co = false;
        private Font fontForAll = null;
        private StyleManager managerStyle = new DevComponents.DotNetBar.StyleManager();

        public void ReadSetting()
        {
            XmlReader reader = XmlReader.Create("Settings.xml");
            while (reader.Read())
            {
                if (reader.NodeType == XmlNodeType.Element
                && reader.Name == "font")
                {
                    string name = reader.GetAttribute(0).ToString();
                    string style = reader.GetAttribute(1).ToString();
                    float size = float.Parse(reader.GetAttribute(2).ToString());

                    if (style == "Bold")
                        fontForAll = new System.Drawing.Font(reader.GetAttribute(0).ToString(), size, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
                    if (style == "Italic")
                        fontForAll = new System.Drawing.Font(reader.GetAttribute(0).ToString(), size, System.Drawing.FontStyle.Italic, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
                    if (style == "Regular")
                        fontForAll = new System.Drawing.Font(reader.GetAttribute(0).ToString(), size, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
                    if (style == "Strikeout")
                        fontForAll = new System.Drawing.Font(reader.GetAttribute(0).ToString(), size, System.Drawing.FontStyle.Strikeout, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
                    if (style == "Underline")
                        fontForAll = new System.Drawing.Font(reader.GetAttribute(0).ToString(), size, System.Drawing.FontStyle.Underline, System.Drawing.GraphicsUnit.Point, ((byte)(0)));

                    while (reader.NodeType != XmlNodeType.EndElement)
                    {
                        reader.Read();
                        if (reader.Name == "managerstyle" && reader.NodeType == XmlNodeType.Element)
                        {
                            reader.Read();
                            string stylemana = reader.Value.ToString();

                            if (stylemana == "Office2007Blue")
                                managerStyle.ManagerStyle = DevComponents.DotNetBar.eStyle.Office2007Blue;
                            if (stylemana == "Office2007Silver")
                                managerStyle.ManagerStyle = DevComponents.DotNetBar.eStyle.Office2007Silver;
                            if (stylemana == "Office2007Black")
                                managerStyle.ManagerStyle = DevComponents.DotNetBar.eStyle.Office2007Black;
                            if (stylemana == "Windows7Blue")
                                managerStyle.ManagerStyle = DevComponents.DotNetBar.eStyle.Windows7Blue;
                            if (stylemana == "Office2007VistaGlass")
                                managerStyle.ManagerStyle = DevComponents.DotNetBar.eStyle.Office2007VistaGlass;
                            if (stylemana == "Office2010Silver")
                                managerStyle.ManagerStyle = DevComponents.DotNetBar.eStyle.Office2010Silver;
                            if (stylemana == "Office2010Blue")
                                managerStyle.ManagerStyle = DevComponents.DotNetBar.eStyle.Office2010Blue;
                            if (stylemana == "Office2010Black")
                                managerStyle.ManagerStyle = DevComponents.DotNetBar.eStyle.Office2010Black;
                        }
                    }
                }
            }
            reader.Close();
        }

        public void ConfigSetting()
        {
            ReadSetting();
            for (int i = 0; i < Controls[0].Controls[1].Controls[2].Controls.Count; i++)
            {
                if (Controls[0].Controls[1].Controls[2].Controls[i] is LabelX)
                {
                    Controls[0].Controls[1].Controls[2].Controls[i].Font = fontForAll;
                }
            }
            styleManager1 = managerStyle;
        }

        public void ResizeGridview()
        {
            if (dgvExDanhSach.Columns.Count > 0)
            {
                int w = dgvExDanhSach.Size.Width;
                int widthC = ((int)w / dgvExDanhSach.Columns.Count);
                if (co == true)
                {
                    for (int i = 0; i < dgvExDanhSach.Columns.Count; i++)
                    {
                        dgvExDanhSach.Columns[i].Width = widthC;
                    }
                }
            }
        }

        private void dgvExDanhSach_RowHeaderMouseClick(object sender, DataGridViewCellMouseEventArgs e)
        {
             // datagridview header click
            int row;
            DateTime dt;
            try
            {        
                row = e.RowIndex;                          
                txtXName.Text = dgvExDanhSach.Rows[row].Cells[1].Value.ToString();
                dt = Convert.ToDateTime(dgvExDanhSach.Rows[row].Cells[2].Value);
                cmbExDay.Text = dt.Day.ToString();
                cmbExMonth.Text = dt.Month.ToString();
                txtXYear.Text = dt.Year.ToString();
                cmbExGender.Text = dgvExDanhSach.Rows[row].Cells[3].Value.ToString();                              
            }
            catch
            {
                row = 0;
            }        
        }

        private void dgvExDanhSach_RowEnter(object sender, DataGridViewCellEventArgs e)
        {
            // datagridview header click
            int row;
            DateTime dt;
            try
            {
                row = e.RowIndex;
                txtUserName.Text = dgvExDanhSach.Rows[row].Cells[0].Value.ToString();
                txtPassword.Text =Md5.DecryptString(dgvExDanhSach.Rows[row].Cells[1].Value.ToString());
                txtXName.Text = dgvExDanhSach.Rows[row].Cells[2].Value.ToString();
                dt = Convert.ToDateTime(dgvExDanhSach.Rows[row].Cells[3].Value);
                cmbExDay.Text = dt.Day.ToString();
                cmbExMonth.Text = dt.Month.ToString(); 
                txtXYear.Text = dt.Year.ToString();
                cmbExGender.Text = dgvExDanhSach.Rows[row].Cells[4].Value.ToString();
                cmbExLevel.Text = dgvExDanhSach.Rows[row].Cells[5].Value.ToString();                  
            }
            catch
            {
                row = 0; 
            }
        }

        public void LoadAccountData()
        {           
            ConnectionController control = new ConnectionController();
            control.connection.Open();
            try
            {
                MySql.Data.MySqlClient.MySqlCommand cmd = control.connection.CreateCommand();
                cmd.CommandText = "SELECT a.UserName, a.Password, a.FullName, a.BirthDay, a.Gender, l.Name as Type   FROM Account a, Level l WHERE a.Level=l.ID ";
                MySql.Data.MySqlClient.MySqlDataAdapter adap = new MySql.Data.MySqlClient.MySqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                adap.Fill(ds);
                dgvExDanhSach.DataSource = ds.Tables[0].DefaultView;

            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (control.connection.State == ConnectionState.Open)
                    control.connection.Clone();
            }           
        }

        public void LoadLevelData()
        {
            ConnectionController control = new ConnectionController();
            control.connection.Open();
            try
            {
                MySql.Data.MySqlClient.MySqlCommand cmd = control.connection.CreateCommand();
                cmd.CommandText = "SELECT *  FROM Level ";
                MySql.Data.MySqlClient.MySqlDataAdapter adap = new MySql.Data.MySqlClient.MySqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                adap.Fill(ds);
                cmbExLevel.DataSource = ds.Tables[0];
                cmbExLevel.DisplayMember = "Name";
                cmbExLevel.ValueMember = "ID";
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (control.connection.State == ConnectionState.Open)
                    control.connection.Clone();
            }
        }

        private void ManageAccount_Resize(object sender, EventArgs e)
        {
            ResizeGridview();

            int wPanel = this.gPanelAction.Size.Width;
            int hPanel = this.gPanelAction.Size.Height;

            int w2Panel = ((int)(wPanel / 2));
            int h2Panel = ((int)(hPanel / 2));

            int wPanel2 = this.gPanelChildAction.Size.Width;
            int hPanel2 = this.gPanelChildAction.Size.Height;

            int w2Panel2 = ((int)(wPanel2 / 2));
            int h2Panel2 = ((int)(hPanel2 / 2));

            int x = w2Panel - w2Panel2;
            int y = h2Panel - h2Panel2;

            this.gPanelChildAction.Location = new Point(x, y);
        }

        private void ManageAccount_FormClosed(object sender, FormClosedEventArgs e)
        {
            Environment.Exit(Environment.ExitCode);
            Application.ExitThread();
            Application.Exit();
        }

        private void ManageAccount_FormClosing(object sender, FormClosingEventArgs e)
        {
            Environment.Exit(Environment.ExitCode);
            Application.ExitThread();
            Application.Exit();
        }

        private void btnXInsert_Click(object sender, EventArgs e)
        {
            if(CheckInput())
            {
                if( ChecUserNameExists())
                {
                    StuMana.UI.Login.Account acc  = GetAccount();
                    AddNewAccount(acc);
                    LoadAccountData();
                    FormReset();
                }
                else
                {
                    MessageBox.Show("Username exists ","Error !!",MessageBoxButtons.OK,MessageBoxIcon.Error);
                    return ;
                }
            }  
        }

        public Boolean AddNewAccount( StuMana.UI.Login.Account acc)
        {
            try
            {
                acc._password = Md5.EncryptString(acc._password);
                ConnectionController connection = new ConnectionController();
                string query = "INSERT INTO Account (UserName, Password, FullName, Birthday, Gender, Level) VALUES(@a1, @a2, @a3, @a4, @a5, @a6)";
                MySqlCommand  cmd = new MySqlCommand(query, connection.connection);
                cmd.Parameters.AddWithValue("@a1", acc._username);
                cmd.Parameters.AddWithValue("@a2", acc._password);
                cmd.Parameters.AddWithValue("@a3", acc._name);
                cmd.Parameters.AddWithValue("@a4", acc._birthDay);
                cmd.Parameters.AddWithValue("@a5", acc._gender);
                cmd.Parameters.AddWithValue("@a6", acc._level);
                if (connection.executeNonQuery(cmd))
                    return true;
                else return false;
            }
            catch (Exception es)
            {
                MessageBox.Show("Loi ket noi co so du lieu \n"+ es.Message);
                return false;
            }
        }

        public bool ChecUserNameExists( )
        {
            bool kq= true;
             ConnectionController control = new ConnectionController();
            control.connection.Open();
            try
            {
                MySql.Data.MySqlClient.MySqlCommand cmd = control.connection.CreateCommand();
                cmd.CommandText = "SELECT *  FROM Account WHERE UserName='" + txtUserName.Text+"'";
                MySql.Data.MySqlClient.MySqlDataAdapter adap = new MySql.Data.MySqlClient.MySqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                adap.Fill(ds);
                if(ds.Tables[0].Rows.Count>0)
                {
                    kq=false;
                }
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (control.connection.State == ConnectionState.Open)
                    control.connection.Clone();
            }
            return kq;
        }

        public bool CheckInput()
        {
            bool kq = true;
            if(txtXName.Text=="")
            {
                MessageBoxEx.Show("Name is empty","Error !!!",MessageBoxButtons.OK,MessageBoxIcon.Error);
                return  false;
            }
            else if(txtUserName.Text=="")
            {
                MessageBoxEx.Show("UserName is empty","Error !!!",MessageBoxButtons.OK,MessageBoxIcon.Error);
                return false ;
            }
            else if (txtPassword.Text=="")
            {
                MessageBoxEx.Show("Password is empty","Error !!!",MessageBoxButtons.OK,MessageBoxIcon.Error);
                return false;
            }
            else if(txtXYear.Text =="")
            {
                MessageBoxEx.Show("Year-birthday is empty","Error !!!",MessageBoxButtons.OK,MessageBoxIcon.Error);
                return false;
            }
            return kq;
        }

        public StuMana.UI.Login.Account GetAccount()
        {
            StuMana.UI.Login.Account acc = new Login.Account();
            acc._username = txtUserName.Text;
            acc._password=txtPassword.Text;
            acc._name=txtXName.Text;
            acc._gender=cmbExGender.Text;
            acc._birthDay=txtXYear.Text+"-"+cmbExMonth.Text+"-"+cmbExDay.Text;
            acc._level=int.Parse( cmbExLevel.SelectedValue.ToString());
            return acc;
        }

        private void btnXUpdate_Click(object sender, EventArgs e)
        {
            if (CheckInput())
            {
                if (!ChecUserNameExists())
                {
                    StuMana.UI.Login.Account acc = GetAccount();
                    UpdateNewAccount(acc);
                    LoadAccountData();
                    FormReset();
                }
                else
                {
                    MessageBoxEx.Show("Username no exists ", "Error !!", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                }
            }  
        }

        public Boolean UpdateNewAccount(StuMana.UI.Login.Account acc)
        {
            try
            {
                acc._password = Md5.EncryptString(acc._password);
                ConnectionController connection = new ConnectionController();
                string query = "UPDATE Account SET Password=@a1, FullName=@a2, Birthday=@a3, Gender=@a4, Level=@a5 WHERE UserName=@a6";
                MySqlCommand cmd = new MySqlCommand(query, connection.connection);

                cmd.Parameters.AddWithValue("@a1", acc._password);
                cmd.Parameters.AddWithValue("@a2", acc._name);
                cmd.Parameters.AddWithValue("@a3", acc._birthDay);
                cmd.Parameters.AddWithValue("@a4", acc._gender);
                cmd.Parameters.AddWithValue("@a5", acc._level);
                cmd.Parameters.AddWithValue("@a6", acc._username);
                if (connection.executeNonQuery(cmd))
                    return true;
                else return false;
            }
            catch (Exception es)
            {
                MessageBoxEx.Show("Loi ket noi co so du lieu \n" + es.Message);
                return false;
            }
        }

        private void btnXDelete_Click(object sender, EventArgs e)
        {
            DialogResult dlgMessage = MessageBoxEx.Show(" Are you sure delete username :" +txtUserName.Text, "Warning !!!",MessageBoxButtons.YesNo,MessageBoxIcon.Warning);
            if (dlgMessage == DialogResult.Yes)
            {
                if (!ChecUserNameExists())
                {
                    deleteAccount(txtUserName.Text);
                    LoadAccountData();
                    FormReset();
                }
                else
                {
                    MessageBoxEx.Show("Username no exists ", "Error !!", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                }
            }
                //  "DELETE FROM `student_info`.`account` WHERE `account`.`UserName` = \'\"
        }

        public Boolean deleteAccount(string userName)
        {
            string query;
            try
            {
                
                ConnectionController connection = new ConnectionController();
                query = "Delete FROM Account WHERE UserName ='" + userName + "'";

                MySqlCommand cmd = new MySqlCommand(query, connection.connection);
                if (connection.executeNonQuery(cmd))
                {
                    cmd = null;
                    return true;
                }
                else
                    return false;
            }
            catch (Exception es)
            {
                MessageBoxEx.Show("Loi ket noi co so du lieu \n" + es.Message);
                return false;
            }
        }

        private void btnXReset_Click(object sender, EventArgs e)
        {
            FormReset();
            LoadAccountData();

            txtXName.Text = "";
            txtUserName.Text = "";
            txtPassword.Text = "";
            txtXYear.Text = "";
            cmbExDay.SelectedIndex = 0;
            cmbExGender.SelectedIndex = 0;
            cmbExLevel.SelectedIndex = 0;
            cmbExMonth.SelectedIndex = 0;
        }

        private void btnSearchLevel_Click(object sender, EventArgs e)
        {
            ConnectionController control = new ConnectionController();
            control.connection.Open();
            try
            {
                MySql.Data.MySqlClient.MySqlCommand cmd = control.connection.CreateCommand();
                cmd.CommandText = "SELECT a.UserName, a.Password, a.FullName, a.BirthDay, a.Gender, l.Name   FROM Account a, Level l WHERE a.Level=l.ID and a.Level = " + cmbExLevel.SelectedValue.ToString();
                MySql.Data.MySqlClient.MySqlDataAdapter adap = new MySql.Data.MySqlClient.MySqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                adap.Fill(ds);
                dgvExDanhSach.DataSource = ds.Tables[0].DefaultView;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (control.connection.State == ConnectionState.Open)
                    control.connection.Clone();
            }
        }

        private void btnSearchName_Click(object sender, EventArgs e)
        {
            if (txtXName.Text == "")
            {
                MessageBoxEx.Show(" Name search is empty", "Warning !!", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return;
            }
            else
            {
                ConnectionController control = new ConnectionController();
                control.connection.Open();
                try
                {
                    MySql.Data.MySqlClient.MySqlCommand cmd = control.connection.CreateCommand();
                    cmd.CommandText = "SELECT a.UserName, a.Password, a.FullName, a.BirthDay, a.Gender, l.Name   FROM Account a, Level l WHERE a.Level=l.ID and a.FullName like N'%" + txtXName.Text + "%'";
                    MySql.Data.MySqlClient.MySqlDataAdapter adap = new MySql.Data.MySqlClient.MySqlDataAdapter(cmd);
                    DataSet ds = new DataSet();
                    adap.Fill(ds);
                    dgvExDanhSach.DataSource = ds.Tables[0].DefaultView;

                }
                catch (Exception)
                {
                    throw;
                }
                finally
                {
                    if (control.connection.State == ConnectionState.Open)
                        control.connection.Clone();
                }
            }
        }   
    }
}
